/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package controller;

import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import model.Capuy;
import model.Danhhieu;
import model.Dantoc;
import model.Dienbienhocham;
import model.Dienbienhocvi;
import model.Dienbienluong;
import model.Donviquanly;
import model.Hesoluong;
import model.Hocham;
import model.Hocvi;
import model.Phanquyen;
import model.Phongban;
import model.Quatrinhdinuocngoai;
import model.Quatrinhnghiencuu;
import model.Quocgia;
import model.Sachxuatban;
import model.Soyeulylich;
import model.Tongiao;
import model.Xuatthan;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import util.HibernateUtil;

/**
 *
 * @author DAT
 */
public class ManagerController extends StaffController {

    public ManagerController() {
    }

    //cập nhật thông tin cán bộ
    //Lập báo cáo
    //Xem báo cáo
    //Tra cứu thông tin
    //-------------------------------------------------------------------------------------
    //Lấy thông tin cán bộ theo các bộ lọc khác nhau
    //-------------------------------------------------------------------------------------
    //Danh sách tất cả các cán bộ
    public static List<Soyeulylich> getAllStaff_Manager() {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        List<Soyeulylich> staffList = new ArrayList<Soyeulylich>();
        try {
            tx = session.beginTransaction();
            List list = session.createQuery("from Soyeulylich").list();
            for (Iterator interator = list.iterator(); interator.hasNext();) {
                Soyeulylich dt = (Soyeulylich) interator.next();
                staffList.add(dt);
            }
            tx.commit();

        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        }
        return staffList;
    }

    /**
     * Tìm kiếm danh sách cán bộ theo tên
     *
     * @param name
     * @param idVien
     * @return
     */
    public static List<Soyeulylich> getStaffsByName_Manager(String name, int idVien) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        List<Soyeulylich> staffList = new ArrayList<Soyeulylich>();
        String hoten = name.toLowerCase();
        String hql = null;
        try {
            tx = session.beginTransaction();
            if (idVien == 0) {
                hql = "from Soyeulylich where lower(hoten) like'%" + hoten + "%'";
            } else {
                hql = "from Soyeulylich where lower(hoten) like'%" + hoten + "%' and iddonviquanly= " + idVien;
            }
            Query query = session.createQuery(hql);
            List list = query.list();
            for (Iterator interator = list.iterator(); interator.hasNext();) {
                Soyeulylich soyeu = (Soyeulylich) interator.next();
                staffList.add(soyeu);
            }
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        }
        return staffList;
    }

    /**
     * Tìm kiếm cán bộ theo tên đăng nhập
     *
     * @param userName - tên đăng nhập
     * @return Cán bộ
     */
    public static Soyeulylich getStaffByUsername_Manager(String userName) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        List<Soyeulylich> staffList = new ArrayList<Soyeulylich>();
        try {
            tx = session.beginTransaction();
            String hql = "from Soyeulylich where tendangnhap = :userName";
            Query query = session.createQuery(hql);
            query.setString("userName", userName);
            List list = query.list();
            for (Iterator interator = list.iterator(); interator.hasNext();) {
                Soyeulylich soyeu = (Soyeulylich) interator.next();
                staffList.add(soyeu);
            }
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        }
        return staffList.get(0);
    }

    /**
     * Tìm cán bộ theo id Cán bộ
     *
     * @param id Cán bộ
     * @return
     */
    public static Soyeulylich getStaffById_Manager(int id) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Soyeulylich staff = null;
        try {
            staff = (Soyeulylich) session.get(Soyeulylich.class, id);
        } catch (HibernateException e) {
            System.err.println(e);
        }
        return staff;
    }

    /**
     * Tìm kiếm danh sách cán bộ theo id Viện
     *
     * @param idInstitute - mã Đơn vị quản lý
     * @return Danh sách cán bộ thuộc đơn vị quản lý
     */
    public static List<Soyeulylich> getStaffsByIdInstitute_Manager(int idInstitute) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        List<Soyeulylich> staffList = new ArrayList<Soyeulylich>();
        try {
            tx = session.beginTransaction();
            String hql = "from Soyeulylich where iddonviquanly =" + idInstitute + "order by idchucvu asc, idhocham asc, idhocvi asc";
            Query query = session.createQuery(hql);
            List list = query.list();
            for (Iterator interator = list.iterator(); interator.hasNext();) {
                Soyeulylich soyeu = (Soyeulylich) interator.next();
                staffList.add(soyeu);
            }
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        }
        return staffList;
    }

    /**
     * Tìm kiếm danh sách cán bộ theo id Viện và id Quyền hạn
     *
     * @param idInstitute
     * @param idRole
     * @return danh sách cán bộ
     */
    public static List<Soyeulylich> getStaffsByIdInstituteAndIdRole_Manager(int idInstitute, int idRole) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        List<Soyeulylich> staffList = new ArrayList<Soyeulylich>();
        try {
            tx = session.beginTransaction();
            String hql = "from Soyeulylich where iddonviquanly =" + idInstitute + "and idphanquyen=" + idRole;
            Query query = session.createQuery(hql);
            List list = query.list();
            for (Iterator interator = list.iterator(); interator.hasNext();) {
                Soyeulylich soyeu = (Soyeulylich) interator.next();
                staffList.add(soyeu);
            }
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        }
        return staffList;
    }

    /**
     * Danh sách cán bộ trong một Viện
     *
     * @param idInstitute
     * @param idChucVu
     * @return
     */
    public static List<Soyeulylich> getStaffsByIdInstituteAndIdChucVu_Manager(int idInstitute, int idChucVu) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        List<Soyeulylich> staffList = new ArrayList<Soyeulylich>();
        try {
            tx = session.beginTransaction();
            String hql = "from Soyeulylich where iddonviquanly =" + idInstitute + "and idchucvu=" + idChucVu;
            Query query = session.createQuery(hql);
            List list = query.list();
            for (Iterator interator = list.iterator(); interator.hasNext();) {
                Soyeulylich soyeu = (Soyeulylich) interator.next();
                staffList.add(soyeu);
            }
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        }
        return staffList;
    }

    //----------------------------------------------------------------------------------------
    ///cập nhật  cán bộ
    //----------------------------------------------------------------------------------------
    //thêm cán bộ
    public static void addStaff_Manager(Soyeulylich staff) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        try {
            tx = session.beginTransaction();
            session.save(staff);
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    // cập nhật thông tin
     /*
     * @param idSoyeu
     * @param hoten
     * @param dvql
     * @param pb
     * @param cmnd
     * @param gioitinh
     * @param tenthuongdung
     * @param ngaysinh
     * @param noisinh
     * @param quequan
     * @param noiohiennay
     * @param xuatthan
     * @param dantoc
     * @param tongiao
     * @param ngayvaodangdubi
     * @param ngayvaodangchinhthuc
     * @param cntn1
     * @param namtn1
     * @param cntn2
     * @param namtn2
     * @param cntn3
     * @param namtn3
     */
    public static void updateStaff_Manager(int idSoyeu, String hoten, Donviquanly dvql, Phongban pb, String cmnd, String gioitinh,
            String tenthuongdung, Date ngaysinh, String noisinh, String quequan, String noiohiennay,
            Xuatthan xuatthan, Dantoc dantoc, Tongiao tongiao, String ngayvaodangdubi, String ngayvaodangchinhthuc,
            String cntn1, String namtn1, String cntn2, String namtn2, String cntn3, String namtn3) {

        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        Soyeulylich soyeu = (Soyeulylich) session.get(Soyeulylich.class, idSoyeu);
        try {
            tx = session.beginTransaction();

            soyeu.setHoten(hoten);
            soyeu.setDonviquanly(dvql);
            soyeu.setPhongban(pb);
            soyeu.setChungminhnhandan(cmnd);
            soyeu.setGioitinh(gioitinh);
            soyeu.setTenthuongdung(tenthuongdung);
            soyeu.setNgaysinh(ngaysinh);
            soyeu.setNoisinh(noisinh);
            soyeu.setQuequan(quequan);
            soyeu.setNoiohiennay(noiohiennay);
            soyeu.setXuatthan(xuatthan);
            soyeu.setDantoc(dantoc);
            soyeu.setTongiao(tongiao);
            soyeu.setNgayvaodangdubi(ngayvaodangdubi);
            soyeu.setNgayvaodangchinhthuc(ngayvaodangchinhthuc);
            soyeu.setTencntn1(cntn1);
            soyeu.setNamtn1(namtn1);
            soyeu.setTencntn2(cntn2);
            soyeu.setNamtn2(namtn2);
            soyeu.setTencntn3(cntn3);
            soyeu.setNamtn3(namtn3);

            session.update(soyeu);
            session.flush();
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    /**
     * Xóa cán bộ trong diễn biến học hàm
     *
     * @param idSoyeu
     */
    public static void deleteStaffInDBHH_Manager(int idSoyeu) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        try {
            tx = session.beginTransaction();
            String hql = "delete from Dienbienhocham where idsoyeulylich = " + idSoyeu;
            Query q = session.createQuery(hql);
            int row = q.executeUpdate();
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
        } finally {
            session.close();
        }
    }

    /**
     * Xóa cán bộ trong danh sách học vị
     *
     * @param idSoyeu
     */
    public static void deleteStaffInDBHV_Manager(int idSoyeu) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        deleteStaffInDBHH_Manager(idSoyeu);
        //deleteStaffInDBHV_Manager(idSoyeu);
        try {
            tx = session.beginTransaction();
            String hql = "delete from Dienbienhocvi where idsoyeulylich = " + idSoyeu;
            Query q = session.createQuery(hql);
            int row = q.executeUpdate();
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
        } finally {
            session.close();
        }
    }

    /**
     * Xóa cán bộ trong Quá trình đi nước ngoài
     *
     * @param idSoyeu
     */
    public static void deleteStaffInQTDNN_Manager(int idSoyeu) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        deleteStaffInDBHH_Manager(idSoyeu);
        deleteStaffInDBHV_Manager(idSoyeu);
        try {
            tx = session.beginTransaction();
            String hql = "delete from Quatrinhdinuocngoai where idsoyeulylich = " + idSoyeu;
            Query q = session.createQuery(hql);
            int row = q.executeUpdate();
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
        } finally {
            session.close();
        }
    }

    /**
     * Xóa cán bộ tỏng quá trình nghiên cứu
     *
     * @param idSoyeu
     */
    public static void deleteStaffInQTNN_Manager(int idSoyeu) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        deleteStaffInDBHH_Manager(idSoyeu);
        deleteStaffInDBHV_Manager(idSoyeu);
        try {
            tx = session.beginTransaction();
            String hql = "delete from Quatrinhnghiencuu where idsoyeulylich = " + idSoyeu;
            Query q = session.createQuery(hql);
            int row = q.executeUpdate();
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
        } finally {
            session.close();
        }
    }

    /**
     * Xóa cán bộ trong diễn biến lương
     *
     * @param idSoyeu
     */
    public static void deleteStaffInDienBienLuong_Manager(int idSoyeu) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        deleteStaffInDBHH_Manager(idSoyeu);
        deleteStaffInDBHV_Manager(idSoyeu);
        deleteStaffInQTDNN_Manager(idSoyeu);
        //deleteStaffInQTNN_Manager(idSoyeu);
        //deleteStaffInDienBienLuong_Manager(idSoyeu);
        try {
            tx = session.beginTransaction();
            String hql = "delete from Dienbienluong where idsoyeulylich = " + idSoyeu;
            Query q = session.createQuery(hql);
            int row = q.executeUpdate();
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
        } finally {
            session.close();
        }
    }

    /**
     * Xóa cán bộ
     *
     * @param staff
     */
    public static void deleteStaff_Manager(int idSoyeu) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        Soyeulylich soyeu = (Soyeulylich) session.get(Soyeulylich.class, idSoyeu);
        try {
            tx = session.beginTransaction();
            session.delete(soyeu);
            session.flush();
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
        } finally {
            session.close();
        }
    }

    /**
     *
     * @return
     */
    public static List<Hocham> getAllHocham_Manager() {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        List<Hocham> hochamList = new ArrayList<Hocham>();
        try {
            tx = session.beginTransaction();
            List list = session.createQuery("from Hocham").list();
            for (Iterator interator = list.iterator(); interator.hasNext();) {
                Hocham hh = (Hocham) interator.next();
                hochamList.add(hh);
            }
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        }
        return hochamList;
    }

    /**
     *
     * @param id
     * @return
     */
    public static Hocham getHochamById_Manager(int id) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Hocham hh = null;
        try {
            hh = (Hocham) session.get(Hocham.class, id);
        } catch (HibernateException e) {
            System.err.println(e);
        }
        return hh;
    }

    /**
     *
     * @return
     */
    public static List<Hocvi> getAllHocvi_Manager() {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        List<Hocvi> hocviList = new ArrayList<Hocvi>();
        try {
            tx = session.beginTransaction();
            List list = session.createQuery("from Hocvi").list();
            for (Iterator iterator = list.iterator(); iterator.hasNext();) {
                Hocvi hv = (Hocvi) iterator.next();
                hocviList.add(hv);
            }
            tx.commit();

        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        }
        return hocviList;
    }

    /**
     *
     * @param id
     * @return
     */
    public static Hocvi getHocviById_Manager(int id) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Hocvi hv = null;
        try {
            hv = (Hocvi) session.get(Hocvi.class, id);
        } catch (HibernateException e) {
            System.err.println(e);
        }
        return hv;
    }

    /**
     *
     * @return
     */
    public static List<Hesoluong> getAllHesoluong_Manager() {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        List<Hesoluong> hesoluongList = new ArrayList<Hesoluong>();
        try {
            tx = session.beginTransaction();
            List list = session.createQuery("from Hesoluong").list();
            for (Iterator iterator = list.iterator(); iterator.hasNext();) {
                Hesoluong hsl = (Hesoluong) iterator.next();
                hesoluongList.add(hsl);
            }
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        }
        return hesoluongList;
    }

    /**
     *
     * @param id
     * @return
     */
    public static Hesoluong getHesoluongById_Manager(int id) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Hesoluong hsl = null;
        try {
            hsl = (Hesoluong) session.get(Hesoluong.class, id);
        } catch (HibernateException e) {
            System.err.println(e);
        }
        return hsl;
    }

    /**
     *
     * @return
     */
    public static List<Capuy> getAllCapuy_Manager() {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        List<Capuy> capuyList = new ArrayList<Capuy>();
        try {
            tx = session.beginTransaction();
            List list = session.createQuery("from Capuy").list();
            for (Iterator iterator = list.iterator(); iterator.hasNext();) {
                Capuy capuy = (Capuy) iterator.next();
                capuyList.add(capuy);
            }
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
        }
        return capuyList;
    }

    /**
     *
     * @param id
     * @return
     */
    public static Capuy getCapuyById_Manager(int id) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Capuy capuy = null;
        try {
            capuy = (Capuy) session.get(Capuy.class, id);
        } catch (HibernateException e) {
            System.err.println(e);
        }
        return capuy;
    }

    /**
     *
     * @return
     */
    public static List<Danhhieu> getAllDanhhieu_Manager() {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = session.beginTransaction();
        List<Danhhieu> danhhieuList = new ArrayList<Danhhieu>();
        try {
            tx = session.beginTransaction();
            List list = session.createQuery("from Danhhieu").list();
            for (Iterator iterator = list.iterator(); iterator.hasNext();) {
                Danhhieu danhhieu = (Danhhieu) iterator.next();
                danhhieuList.add(danhhieu);
            }
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
        }
        return danhhieuList;
    }

    /**
     *
     * @param id
     * @return
     */
    public static Danhhieu getDanhhieuById_Manager(int id) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Danhhieu dh = null;
        try {
            dh = (Danhhieu) session.get(Danhhieu.class, id);
        } catch (HibernateException e) {
            System.err.println(e);
        }
        return dh;
    }

    /*
     * 
     */
    public static List<Quocgia> getAllQuocgia_Manager() {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = session.beginTransaction();
        List<Quocgia> quocgiaList = new ArrayList<Quocgia>();
        try {
            tx = session.beginTransaction();
            List list = session.createQuery("from Quocgia").list();
            for (Iterator iterator = list.iterator(); iterator.hasNext();) {
                Quocgia quocgia = (Quocgia) iterator.next();
                quocgiaList.add(quocgia);
            }
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
        }
        return quocgiaList;
    }

    /**
     *
     * @param id
     * @return
     */
    public static Quocgia getQuocgiaById_Manager(int id) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Quocgia qg = null;
        try {
            qg = (Quocgia) session.get(Quocgia.class, id);
        } catch (HibernateException e) {
            System.err.println(e);
        }
        return qg;
    }

    /**
     *
     * @param idStaff
     * @return
     */
    public static List<Dienbienhocham> getDienBienHocHamByIdStaff_Manager(int idStaff) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        List<Dienbienhocham> dbhhList = new ArrayList<Dienbienhocham>();
        try {
            tx = session.beginTransaction();
            String hql = "from Dienbienhocham where idsoyeulylich =" + idStaff + " order by idhocham desc";
            Query query = session.createQuery(hql);
            List list = query.list();
            for (Iterator interator = list.iterator(); interator.hasNext();) {
                Dienbienhocham dbhh = (Dienbienhocham) interator.next();
                dbhhList.add(dbhh);
            }
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        }
        return dbhhList;
    }

    /**
     *
     * @param idStaff
     * @return
     */
    public static List<Dienbienhocvi> getDienBienHocViByIdStaff_Manager(int idStaff) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        List<Dienbienhocvi> dbhvList = new ArrayList<Dienbienhocvi>();
        try {
            tx = session.beginTransaction();
            String hql = "from Dienbienhocvi where idsoyeulylich =" + idStaff + " order by idhocvi desc";
            Query query = session.createQuery(hql);
            List list = query.list();
            for (Iterator interator = list.iterator(); interator.hasNext();) {
                Dienbienhocvi dbhv = (Dienbienhocvi) interator.next();
                dbhvList.add(dbhv);
            }
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        }
        return dbhvList;
    }

    /**
     *
     * @param idStaff
     * @return
     */
    public static List<Quatrinhdinuocngoai> getQTDNNByIdStaff_Manager(int idStaff) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        List<Quatrinhdinuocngoai> qtdnnList = new ArrayList<Quatrinhdinuocngoai>();
        try {
            tx = session.beginTransaction();
            String hql = "from Quatrinhdinuocngoai where idsoyeulylich =" + idStaff + " order by tungay asc";
            Query query = session.createQuery(hql);
            List list = query.list();
            for (Iterator interator = list.iterator(); interator.hasNext();) {
                Quatrinhdinuocngoai qtdnn = (Quatrinhdinuocngoai) interator.next();
                qtdnnList.add(qtdnn);
            }
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        }
        return qtdnnList;
    }

    /**
     *
     * @param idStaff
     * @return
     */
    public static List<Quatrinhnghiencuu> getQTNNByIdStaff_Manager(int idStaff) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        List<Quatrinhnghiencuu> qtnnList = new ArrayList<Quatrinhnghiencuu>();
        try {
            tx = session.beginTransaction();
            String hql = "from Quatrinhnghiencuu where idsoyeulylich =" + idStaff + " order by tungay asc";
            Query query = session.createQuery(hql);
            List list = query.list();
            for (Iterator interator = list.iterator(); interator.hasNext();) {
                Quatrinhnghiencuu qtnn = (Quatrinhnghiencuu) interator.next();
                qtnnList.add(qtnn);
            }
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        }
        return qtnnList;
    }

    /**
     *
     * @param idStaff
     * @return
     */
    public static List<Dienbienluong> getDienBienLuongByIdStaff(int idStaff) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        List<Dienbienluong> dienBienLuongList = new ArrayList<Dienbienluong>();
        try {
            tx = session.beginTransaction();
            String hql = "from Dienbienluong where idsoyeulylich =" + idStaff + " order by tungay asc";
            Query query = session.createQuery(hql);
            List list = query.list();
            for (Iterator interator = list.iterator(); interator.hasNext();) {
                Dienbienluong dbl = (Dienbienluong) interator.next();
                dienBienLuongList.add(dbl);
            }
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        }
        return dienBienLuongList;
    }

    /**
     *
     * @param idStaff
     * @param idHocvi
     * @param namPhong
     */
    public static void addDienBienHocVi_Manager(int idStaff, int idHocvi, String namPhong) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        Dienbienhocvi dbhv = new Dienbienhocvi();
        try {
            tx = session.beginTransaction();
            dbhv.setHocvi((Hocvi) session.get(Hocvi.class, idHocvi));
            dbhv.setSoyeulylich((Soyeulylich) session.get(Soyeulylich.class, idStaff));
            dbhv.setNamphong(namPhong);
            session.save(dbhv);
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    /**
     *
     * @param idStaff
     * @param idHocham
     * @param chuyenNganh
     * @param namPhong
     */
    public static void addDienBienHocHam_Manager(int idStaff, int idHocham, String chuyenNganh, String namPhong) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        Dienbienhocham dbhh = new Dienbienhocham();
        try {
            tx = session.beginTransaction();
            dbhh.setHocham((Hocham) session.get(Hocham.class, idHocham));
            dbhh.setSoyeulylich((Soyeulylich) session.get(Soyeulylich.class, idStaff));
            dbhh.setChuyennganh(chuyenNganh);
            dbhh.setNamphong(namPhong);
            session.save(dbhh);
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    /**
     *
     * @param idStaff
     * @param tendetai
     * @param coquanchutri
     * @param tungay
     * @param denngay
     * @param ngaynghiemthu
     */
    public static void addQuaTrinhNghienCuu_Manager(int idStaff, String tendetai, String coquanchutri, Date tungay, Date denngay, Date ngaynghiemthu) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        Quatrinhnghiencuu qtnn = new Quatrinhnghiencuu();
        try {
            tx = session.beginTransaction();
            qtnn.setSoyeulylich((Soyeulylich) session.get(Soyeulylich.class, idStaff));
            qtnn.setTendetai(tendetai);
            qtnn.setCoquanchutri(coquanchutri);
            qtnn.setTungay(tungay);
            qtnn.setDenngay(denngay);
            qtnn.setNgaynhiemthu(ngaynghiemthu);
            session.save(qtnn);
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    /**
     *
     * @param idStaff
     * @param mucdich
     * @param idQuocgia
     * @param tungay
     * @param denngay
     */
    public static void addQuaTrinhDiNuocNgoai_Manager(int idStaff, String mucdich, int idQuocgia, Date tungay, Date denngay) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        Quatrinhdinuocngoai qtdnn = new Quatrinhdinuocngoai();
        try {
            tx = session.beginTransaction();
            qtdnn.setSoyeulylich((Soyeulylich) session.get(Soyeulylich.class, idStaff));
            qtdnn.setMucdich(mucdich);
            qtdnn.setQuocgia((Quocgia) session.get(Quocgia.class, idQuocgia));
            qtdnn.setTungay(tungay);
            qtdnn.setDenngay(denngay);

            session.save(qtdnn);
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    /**
     *
     * @param idStaff
     * @param idHesoluong
     * @param tungay
     * @param denngay
     */
    public static void addDienBienLuong_Manager(int idStaff, int idHesoluong, Date tungay, Date denngay) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        Dienbienluong dbl = new Dienbienluong();
        try {
            tx = session.beginTransaction();
            dbl.setSoyeulylich((Soyeulylich) session.get(Soyeulylich.class, idStaff));
            dbl.setHesoluong((Hesoluong) session.get(Hesoluong.class, idHesoluong));
            dbl.setTungay(tungay);
            dbl.setDenngay(denngay);
            session.save(dbl);
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    /**
     *
     * @param idDBHV
     * @return
     */
    public static Dienbienhocvi getDienBienHocViById_Manager(int idDBHV) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Dienbienhocvi dbhv = null;
        try {
            dbhv = (Dienbienhocvi) session.get(Dienbienhocvi.class, idDBHV);
        } catch (HibernateException e) {
            System.err.println(e);
        }
        return dbhv;
    }

    /**
     *
     * @param idDBHH
     * @return
     */
    public static Dienbienhocham getDienBienHocHamById_Manager(int idDBHH) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Dienbienhocham dbhh = null;
        try {
            dbhh = (Dienbienhocham) session.get(Dienbienhocham.class, idDBHH);
        } catch (HibernateException e) {
            System.err.println(e);
        }
        return dbhh;
    }

    /**
     *
     * @param idQTDNN
     * @return
     */
    public static Quatrinhdinuocngoai getQTDNNById_Manager(int idQTDNN) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Quatrinhdinuocngoai qtdnn = null;
        try {
            qtdnn = (Quatrinhdinuocngoai) session.get(Quatrinhdinuocngoai.class, idQTDNN);
        } catch (HibernateException e) {
            System.err.println(e);
        }
        return qtdnn;
    }

    /**
     *
     * @param idQTNN
     * @return
     */
    public static Quatrinhnghiencuu getQTNNById_Manager(int idQTNN) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Quatrinhnghiencuu qtnn = null;
        try {
            qtnn = (Quatrinhnghiencuu) session.get(Quatrinhnghiencuu.class, idQTNN);
        } catch (HibernateException e) {
            System.err.println(e);
        }
        return qtnn;
    }

    /**
     *
     * @param idDBL
     * @return
     */
    public static Dienbienluong getDienBienLuongById_Manager(int idDBL) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Dienbienluong dbl = null;
        try {
            dbl = (Dienbienluong) session.get(Dienbienluong.class, idDBL);
        } catch (HibernateException e) {
            System.err.println(e);
        }
        return dbl;
    }

    /**
     *
     * @param idDBHV
     * @param idHocvi
     * @param namphong
     */
    public static void updateDienBienHocVi_Manager(int idDBHV, int idHocvi, String namphong) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        Dienbienhocvi dbhv = (Dienbienhocvi) session.get(Dienbienhocvi.class, idDBHV);
        try {
            tx = session.beginTransaction();
            dbhv.setHocvi((Hocvi) session.get(Hocvi.class, idHocvi));
            dbhv.setNamphong(namphong);
            session.update(dbhv);
            session.flush();
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    /**
     *
     * @param idDBHH
     * @param idHocham
     * @param namphong
     * @param chuyennganh
     */
    public static void updateDienBienHocHam_Manager(int idDBHH, int idHocham, String namphong, String chuyennganh) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        Dienbienhocham dbhh = (Dienbienhocham) session.get(Dienbienhocham.class, idDBHH);
        try {
            tx = session.beginTransaction();
            dbhh.setHocham((Hocham) session.get(Hocham.class, idHocham));
            dbhh.setNamphong(namphong);
            dbhh.setChuyennganh(chuyennganh);
            session.update(dbhh);
            session.flush();
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    /**
     *
     * @param idQTNN
     * @param tendetai
     * @param coquanchutri
     * @param tungay
     * @param dengnay
     * @param ngaynghiemthu
     */
    public static void updateQuaTrinhNghienCuu_Manager(int idQTNN, String tendetai, String coquanchutri, Date tungay, Date dengnay, Date ngaynghiemthu) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        Quatrinhnghiencuu qtnn = (Quatrinhnghiencuu) session.get(Quatrinhnghiencuu.class, idQTNN);
        try {
            tx = session.beginTransaction();
            qtnn.setTendetai(tendetai);
            qtnn.setCoquanchutri(coquanchutri);
            qtnn.setTungay(tungay);
            qtnn.setDenngay(dengnay);
            qtnn.setNgaynhiemthu(ngaynghiemthu);
            session.update(qtnn);
            session.flush();
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    /**
     *
     * @param idQTDNN
     * @param mucdich
     * @param idQuocgia
     * @param tungay
     * @param dengnay
     */
    public static void updateQuaTrinhDiNuocNgoai_Manager(int idQTDNN, String mucdich, int idQuocgia, Date tungay, Date dengnay) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        Quatrinhdinuocngoai qtdnn = (Quatrinhdinuocngoai) session.get(Quatrinhdinuocngoai.class, idQTDNN);
        try {
            tx = session.beginTransaction();
            qtdnn.setMucdich(mucdich);
            qtdnn.setQuocgia((Quocgia) session.get(Quocgia.class, idQuocgia));
            qtdnn.setTungay(tungay);
            qtdnn.setDenngay(dengnay);
            session.update(qtdnn);
            session.flush();
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    /**
     *
     * @param idDBL
     * @param idHesoluong
     * @param tungay
     * @param dengnay
     */
    public static void updateDienBienLuong_Manager(int idDBL, int idHesoluong, Date tungay, Date dengnay) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        Dienbienluong dbl = (Dienbienluong) session.get(Dienbienluong.class, idDBL);
        try {
            tx = session.beginTransaction();
            dbl.setHesoluong((Hesoluong) session.get(Hesoluong.class, idHesoluong));
            dbl.setTungay(tungay);
            dbl.setDenngay(dengnay);
            session.update(dbl);
            session.flush();
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    /**
     *
     * @param idDBHV
     */
    public static void deleteDienBienHocVi_Manager(int idDBHV) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        Dienbienhocvi dbhv = (Dienbienhocvi) session.get(Dienbienhocvi.class, idDBHV);
        try {
            tx = session.beginTransaction();
            session.delete(dbhv);
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    /**
     *
     * @param idDBHH
     */
    public static void deleteDienBienHocHam_Manager(int idDBHH) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        Dienbienhocham dbhh = (Dienbienhocham) session.get(Dienbienhocham.class, idDBHH);
        try {
            tx = session.beginTransaction();
            session.delete(dbhh);
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    /**
     *
     * @param idQTNN
     */
    public static void deleteQuaTrinhNghienCuu_Manager(int idQTNN) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        Quatrinhnghiencuu qtnn = (Quatrinhnghiencuu) session.get(Quatrinhnghiencuu.class, idQTNN);
        try {
            tx = session.beginTransaction();
            session.delete(qtnn);
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    /**
     *
     * @param idQTDNN
     */
    public static void deleteQuaTrinhDiNuocNgoai_Manager(int idQTDNN) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        Quatrinhdinuocngoai qtdnn = (Quatrinhdinuocngoai) session.get(Quatrinhdinuocngoai.class, idQTDNN);
        try {
            tx = session.beginTransaction();
            session.delete(qtdnn);
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    /**
     *
     * @param idDBL
     */
    public static void deleteDienBienLuong_Manager(int idDBL) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        Dienbienluong dbl = (Dienbienluong) session.get(Dienbienluong.class, idDBL);
        try {
            tx = session.beginTransaction();
            session.delete(dbl);
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    /**
     *
     * @param idStaff
     * @return
     */
    public static List<Sachxuatban> sachXuatBan_Manager(int idStaff) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        List<Sachxuatban> sxbList = new ArrayList<Sachxuatban>();
        try {
            tx = session.beginTransaction();
            String hql = "from Sachxuatban where idsoyeulylich = " + idStaff;
            List list = session.createQuery(hql).list();
            for (Iterator interator = list.iterator(); interator.hasNext();) {
                Sachxuatban sxb = (Sachxuatban) interator.next();
                sxbList.add(sxb);
            }
            tx.commit();

        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        }
        return sxbList;
    }

    /**
     *
     * @return
     */
    public static List<Phanquyen> getAllPhanQuyen_Manager() {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        List<Phanquyen> phanquyenList = new ArrayList<Phanquyen>();
        try {
            tx = session.beginTransaction();
            String hql = "from Phanquyen";
            List list = session.createQuery(hql).list();
            for (Iterator interator = list.iterator(); interator.hasNext();) {
                Phanquyen phanquyen = (Phanquyen) interator.next();
                phanquyenList.add(phanquyen);
            }
            tx.commit();

        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        }
        return phanquyenList;
    }

    /**
     *
     * @param idStaff
     * @param idQuyenhan
     */
    public static void updateQuyenhan_Manager(int idStaff, int idQuyenhan) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        Soyeulylich staff = (Soyeulylich) session.get(Soyeulylich.class, idStaff);
        try {
            tx = session.beginTransaction();
            staff.setPhanquyen((Phanquyen) session.get(Phanquyen.class, idQuyenhan));
            session.update(staff);
            tx.commit();
        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    //***************************************************************************
    //***************************************************************************
    // Các phương thức xử lý với dữ liệu xuất thân của cán bộ
    //***************************************************************************
    //***************************************************************************
    //***************************************************************************
    /**
     *
     * @return Danh sách xuất thân
     */
    public static List<Xuatthan> getAllClass_Manager() {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        List<Xuatthan> classList = new ArrayList<Xuatthan>();
        try {
            tx = session.beginTransaction();
            List list = session.createQuery("from Xuatthan").list();
            for (Iterator interator = list.iterator(); interator.hasNext();) {
                Xuatthan xt = (Xuatthan) interator.next();
                classList.add(xt);
            }
            tx.commit();

        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        }
        return classList;
    }

    /**
     *
     * @param id xuất thấn
     * @return Một đối tượng Xuất thân
     */
    public static Xuatthan getClassById_Manager(int id) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Xuatthan xt = null;
        try {
            xt = (Xuatthan) session.get(Xuatthan.class, id);
        } catch (HibernateException e) {
            System.err.println(e);
        } finally {
            session.close();
        }
        return xt;
    }

    //***************************************************************************
    //***************************************************************************
    // Các phương thức xử lý dữ liệu dân tộc của cán bộ
    //***************************************************************************
    //***************************************************************************
    /**
     *
     * @return Danh sách dân tộc
     */
    public static List<Dantoc> getAllPeople_Manager() {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        List<Dantoc> peopleList = new ArrayList<Dantoc>();
        try {
            tx = session.beginTransaction();
            List list = session.createQuery("from Dantoc").list();
            for (Iterator interator = list.iterator(); interator.hasNext();) {
                Dantoc dt = (Dantoc) interator.next();
                peopleList.add(dt);
            }
            tx.commit();

        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        }
        return peopleList;
    }

    /**
     *
     * @param id dân tộc
     * @return đối tượng Dân tộc
     */
    public static Dantoc getPeopleById_Manager(int id) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Dantoc dt = null;
        try {
            dt = (Dantoc) session.get(Dantoc.class, id);
        } catch (HibernateException e) {
            System.err.println(e);
        } finally {
            session.close();
        }
        return dt;
    }

    //***************************************************************************
    //***************************************************************************
    // Các phương thức xử lý dữ liệu tôn giáo của cán bộ
    //***************************************************************************
    //***************************************************************************
    /**
     *
     * @return Danh sách tôn giáo
     */
    public static List<Tongiao> getAllReligious_Manager() {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = null;
        List<Tongiao> religiousList = new ArrayList<Tongiao>();
        try {
            tx = session.beginTransaction();
            List list = session.createQuery("from Tongiao").list();
            for (Iterator interator = list.iterator(); interator.hasNext();) {
                Tongiao tg = (Tongiao) interator.next();
                religiousList.add(tg);
            }
            tx.commit();

        } catch (HibernateException e) {
            tx.rollback();
            e.printStackTrace();
        }
        return religiousList;
    }

    /**
     *
     * @param id
     * @return
     */
    public static Tongiao getReligiousById_Manager(int id) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Tongiao religious = null;
        try {
            religious = (Tongiao) session.get(Tongiao.class, id);
        } catch (HibernateException e) {
            e.printStackTrace();
        } finally {
            session.close();
        }
        return religious;
    }
    
}
